package com.sinosoft.system.primary.dao.imp;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.sinosoft.system.primary.dao.SysMenuDao;
import com.sinosoft.system.primary.entity.SysMenu;

   /**
    * @ClassName SysMenuDaoJdbc实现类
    * @date 2017-2-9 
    * @author shangxp
    */  
@Repository
public class SysMenuDaoJdbc implements SysMenuDao {
	private static Logger log = Logger.getLogger(SysMenuDaoJdbc.class);
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public int insertSysMenu(SysMenu sysMenu) {
		String sqlStr = "insert into sys_menu ( "
							+" id ,"	
							+" menu_name ,"	
							+" menu_code ,"	
							+" menu_uri ,"	
							+" menu_img ,"	
							+" menu_bgc ,"	
							+" sortno ,"	
							+" parent_id ,"	
							+" menu_type ,"	
							+"is_del) values( "
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" ?,"
							 +" 0 )";
		log.debug(sqlStr+"\n"+sysMenu.toString());
		try{
			Object[] args = {
					sysMenu.getId(),
					sysMenu.getMenuName(),
					sysMenu.getMenuCode(),
					sysMenu.getMenuUri(),
					sysMenu.getMenuImg(),
					sysMenu.getMenuBgc(),
					sysMenu.getSortno(),
					sysMenu.getParentId(),
					sysMenu.getMenuType()
			};
			return jdbcTemplate.update(sqlStr, args);
		}catch(Exception e){
			log.error("数据添加异常 SQL："+sqlStr+"\n"+sysMenu.toString()+e.getMessage());
		}
		return 0;
	}

	@Override
	public int updateSysMenu(SysMenu sysMenu) {
		String sqlStr = "update sys_menu set "
									+" id = ? , "
									+" menu_name = ? , "
									+" menu_code = ? , "
									+" menu_uri = ? , "
									+" menu_img = ? , "
									+" menu_bgc = ? , "
									+" sortno = ? , "
									+" parent_id = ? , "
									+" menu_type = ?  "
									+" where id = ?";
		log.debug(sqlStr+"\n"+sysMenu.toString());
		try{
			Object[] args = {
					sysMenu.getId(),
					sysMenu.getMenuName(),
					sysMenu.getMenuCode(),
					sysMenu.getMenuUri(),
					sysMenu.getMenuImg(),
					sysMenu.getMenuBgc(),
					sysMenu.getSortno(),
					sysMenu.getParentId(),
					sysMenu.getMenuType(),
					sysMenu.getId()
			};
			return jdbcTemplate.update(sqlStr, args);
		}catch(Exception e){
			log.error("数据更新异常 SQL："+sqlStr+"\n"+sysMenu.toString()+e.getMessage());
		}
		return 0;
	}

	@Override
	public int deleteSysMenu(int id) {
		String sqlStr = "update sys_menu set is_del = 1  where id = ?";
		try{
			Object[] args = {
					id
			};
			return jdbcTemplate.update(sqlStr, args);
		}catch(Exception e){
			log.error("数据删除异常"+sqlStr+"\nID="+id+"\n"+e.getMessage());
		}	return 0;
	}

	@Override
	public SysMenu selectSysMenu(int id) {
		String sqlStr = "select * from sys_menu where is_del =0 and id = ?";
		log.debug(sqlStr+"\nID="+id);
		try{
			Object[] args = {
				id
			};
			return jdbcTemplate.queryForObject(sqlStr, args, new SysMenuMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\nID="+id+"\n"+e.getMessage());
		}	return null;
	}

	@Override
	public SysMenu selectSysMenu(List<String> queryKeys,List<Object> queryValues) {
		StringBuffer sqlStr = new StringBuffer("select * from sys_menu where is_del =0 " );
		Object[] args = null;
		if(queryKeys!=null && !queryKeys.isEmpty()){	
			args = new Object[queryKeys.size()];
			for(int i=0;i<queryKeys.size();i++){
				sqlStr.append(" and "+queryKeys.get(i)+"=? ");
				args[i] = queryValues.get(i);
			}
		}
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.queryForObject(sqlStr.toString(), args, new SysMenuMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public int selectCountSysMenu() {
		String sqlStr = "select count(1) from sys_menu where is_del =0 ";
		log.debug(sqlStr);
		try{
			return jdbcTemplate.queryForObject(sqlStr, Integer.class);
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\n"+e.getMessage());
		}
		return 0;
	}

	@Override
	public List<SysMenu> selectLimitSysMenu(int start, int size) {
		String sqlStr = "select ";
					sqlStr+=" id ,";
					sqlStr+=" menu_name ,";
					sqlStr+=" menu_code ,";
					sqlStr+=" menu_uri ,";
					sqlStr+=" menu_img ,";
					sqlStr+=" menu_bgc ,";
					sqlStr+=" sortno ,";
					sqlStr+=" parent_id ,";
					sqlStr+=" menu_type ";
			sqlStr+=" from sys_menu where is_del =0  order by id desc limit ?,?";
		log.debug(sqlStr);
		try{
		Object[] args = {
				start,
				size
		};
			return jdbcTemplate.query(sqlStr, args, new SysMenuMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public List<SysMenu> selectAllSysMenu() {
		String sqlStr = "select ";
					sqlStr+=" id ,";
					sqlStr+=" menu_name ,";
					sqlStr+=" menu_code ,";
					sqlStr+=" menu_uri ,";
					sqlStr+=" menu_img ,";
					sqlStr+=" menu_bgc ,";
					sqlStr+=" sortno ,";
					sqlStr+=" parent_id ,";
					sqlStr+=" menu_type ";
		 sqlStr+=" from sys_menu where is_del =0 order by id desc ";
		log.debug(sqlStr);
		try{
			return jdbcTemplate.query(sqlStr, new SysMenuMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public int selectCountSysMenu(List<String> queryKeys,List<Object> queryValues) {
		StringBuffer sqlStr = new StringBuffer("select count(1) from sys_menu where is_del = 0 ");
		Object[] args = null;
		if(queryKeys!=null && !queryKeys.isEmpty()){	
			args = new Object[queryKeys.size()];
			for(int i=0;i<queryKeys.size();i++){
				sqlStr.append(" and "+queryKeys.get(i)+" like ? ");
				args[i] = "%"+queryValues.get(i)+"%";
			}
		}		
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.queryForObject(sqlStr.toString(), args,Integer.class);
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return 0;
	}

	@Override
	public List<SysMenu> selectLimitSysMenu(List<String> queryKeys,List<Object> queryValues,int start,int size) {
		StringBuffer sqlStr = new StringBuffer("select ");
					sqlStr.append(" id ,");
					sqlStr.append(" menu_name ,");
					sqlStr.append(" menu_code ,");
					sqlStr.append(" menu_uri ,");
					sqlStr.append(" menu_img ,");
					sqlStr.append(" menu_bgc ,");
					sqlStr.append(" sortno ,");
					sqlStr.append(" parent_id ,");
					sqlStr.append(" menu_type ");
			sqlStr.append(" from sys_menu where is_del =0 " );
		Object[] args = null;
			if(queryKeys!=null && !queryKeys.isEmpty()){	
				args = new Object[queryKeys.size()+2];
				for(int i=0;i<queryKeys.size();i++){
					sqlStr.append(" and "+queryKeys.get(i)+" like ? ");
					args[i] = "%"+queryValues.get(i)+"%";
				}
			}
		if(args==null){
			args = new Object[]{start,size};
		}else{
			args[queryKeys.size()] = start;
			args[queryKeys.size()+1] = size;
		}
		sqlStr.append(" order by id desc ");
		sqlStr.append(" limit ?,?");
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.query(sqlStr.toString(), args, new SysMenuMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return null;
	}

	@Override
	public List<SysMenu> selectAllSysMenu(List<String> queryKeys,List<Object> queryValues) {
		StringBuffer sqlStr = new StringBuffer("select ");
					sqlStr.append(" id ,");
					sqlStr.append(" menu_name ,");
					sqlStr.append(" menu_code ,");
					sqlStr.append(" menu_uri ,");
					sqlStr.append(" menu_img ,");
					sqlStr.append(" menu_bgc ,");
					sqlStr.append(" sortno ,");
					sqlStr.append(" parent_id ,");
					sqlStr.append(" menu_type ");
			sqlStr.append("from sys_menu where is_del =0 " );
		Object[] args = null;
			if(queryKeys!=null && !queryKeys.isEmpty()){	
				args = new Object[queryKeys.size()];
				for(int i=0;i<queryKeys.size();i++){
					sqlStr.append(" and "+queryKeys.get(i)+" like ? ");
					args[i] = "%"+queryValues.get(i)+"%";
				}
			}
		sqlStr.append(" order by id desc ");
		log.debug(sqlStr.toString());
		try{
			return jdbcTemplate.query(sqlStr.toString(), args, new SysMenuMapper());
		}catch(Exception e){
			log.error("数据查询异常"+sqlStr.toString()+"\n"+e.getMessage());
		}
		return null;
	}
	
	public boolean isPermitted(String roleids,String uri){
		Object[] args = null;
		if(roleids!=null&&!"".equals(roleids)){
			String sqlStr = "select count(1) cnt from sys_menu m, sys_role_menu rm where m.is_del=0 and rm.menuid = m.id and m.menu_uri = ? and rm.role_id in (";
			String[] roles = roleids.split(",");
			args = new String[roles.length+1];
			args[0]=uri;
			for(int i=0 ;i<roles.length;i++){
				sqlStr+="'"+roles[i]+"',";
				args[i+1]=roles[i];
			}
			sqlStr = sqlStr.substring(0, sqlStr.lastIndexOf(","));
			sqlStr+=")";
			log.debug(sqlStr.toString());
			try{
				int i = jdbcTemplate.queryForObject(sqlStr, Integer.class); 
				if(i>0){
					return true;
				}else{		
					return false;
				}
			}catch(Exception e){
				log.error(e.getMessage());
			}
		}	
		return false;
	}

	protected class SysMenuMapper implements RowMapper<SysMenu> {
		@Override
		public SysMenu mapRow(ResultSet arg0, int arg1) throws SQLException {
			SysMenu sysMenu = new SysMenu();
			if(arg0!=null){
					sysMenu.setId(arg0.getInt("id"));
					sysMenu.setMenuName(arg0.getString("menu_name"));
					sysMenu.setMenuCode(arg0.getString("menu_code"));
					sysMenu.setMenuUri(arg0.getString("menu_uri"));
					sysMenu.setMenuImg(arg0.getString("menu_img"));
					sysMenu.setMenuBgc(arg0.getString("menu_bgc"));
					sysMenu.setSortno(arg0.getInt("sortno"));
					sysMenu.setParentId(arg0.getInt("parent_id"));
					sysMenu.setMenuType(arg0.getString("menu_type"));
				}
			return sysMenu;
		}  
	}
}

